Imports System.Data
Imports System.Data.SqlClient
Public Class oConfigure
    
    
    Public Shared Function LoadAllDBTable() As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("Select name tables from sys.tables", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Function LoadTableData(ByVal TblName As String) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("Select * from " + TblName, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Sub UpdateConfiguration(ByVal Code As String, ByVal Value As String)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("CONFIGURATION_UPDATE", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@Code", Code)
                MyCommand.Parameters.AddWithValue("@Value", Value)
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
            Else
                MessageBox.Show("Can't connect to database server!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Sub BackupDB(ByVal StrPath As String, ByVal DBName As String)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim str As String
                str = "Backup database " + DBName + " to disk = '" + StrPath + "'"
                Dim MyCommand As SqlCommand = New SqlCommand(str, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
                MessageBox.Show("Backup sucessful!")
            Else
                MessageBox.Show("Can't connect to database server!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Sub RestoreDB(ByVal StrPath As String, ByVal DBName As String)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim sql As String = "RESTORE DATABASE " & DBName & _
                    " FROM DISK = N'" & StrPath & "' " & _
                    "WITH FILE = 1,REPLACE," & _
                    "Move N'TOTALPOS' TO N'" & Application.StartupPath & "\" & DBName & ".MDF'," & _
                    "Move N'TOTALPOS_LOG' TO N'" & Application.StartupPath & "\" & DBName & ".LDF'"
                Dim MyCommand As SqlCommand = New SqlCommand(sql, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
                MessageBox.Show("Restore sucessful!")
            Else
                MessageBox.Show("Can't connect to database server!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Function RunQuery(ByVal QueryString As String) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            Else
                MessageBox.Show("Can't connect to database server!")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
End Class
